//
//  SqliteManager.m
//  SqliteManagerDemo
//
//  Created by US bike on 2017/9/4.
//  Copyright © 2017年 US bike. All rights reserved.
//

#import "SqliteManager.h"

#import <FMDB/FMDB.h>
#import <MJExtension/MJExtension.h>



SqliteManager *shareObj;


@interface SqliteManager()
@property (nonatomic, strong) FMDatabaseQueue *queue;
@end

@implementation SqliteManager


+ (instancetype) shareManager {
    
    
    
    if (!shareObj) {
        shareObj = [SqliteManager new];
        
        // 获取Documents目录路径
        NSString*docDir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) firstObject];
        
        NSString *dbPath = [docDir stringByAppendingPathComponent:DB_NAME];
        shareObj.queue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
        [shareObj createDataTable];
    }
    
    return shareObj;
}



#pragma mark - 私有方法
//创建数据库和数据表
- (void) createDataTable {
    
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        
        Boolean result = [db executeUpdate:SQL_CREATE_TABLE_RECORD];
        if (result) {
            NSLog(@"创建数据表Record成功");
        } else {
            NSLog(@"创建数据表Record失败");
        }
        
        result = [db executeUpdate:SQL_CREATE_TABLE_VIDEORECORD];
        if (result) {
            NSLog(@"创建数据表VideoRecord成功");
        } else {
            NSLog(@"创建数据表VideoRecord失败");
        }
    }];
}


#pragma mark - 共有方法

//新增一条记录
- (void) addRecordItemWithRecord:(KiwipenRecord *) record {
    NSLog(@"插入数据到Record表中");
    NSDictionary *dic = [record mj_keyValues];
    
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        
        NSString *sql = @"";
        Boolean succed = false;
        
        //插入数据到Record表
        sql = @"INSERT INTO Record ( userID, deviceID, roleType, originLanguage, transformLuanguage, originText, transformText, updateTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
        NSLog(@"开始插入数据到Record表中，sql = %@", sql);
        succed = [db executeUpdate:sql, record.userID, record.deviceID, @(record.roleType), record.originLanguage, record.transformLuanguage, record.originText, record.transformText, record.updateTime];
        
        if (succed) {
            NSLog(@"插入数据到Record表成功");
        } else {
            NSLog(@"插入数据到Record表失败");
        }
        
        
        
        //查询RecordID
        sql = @"SELECT recordID From Record ORDER BY updateTime DESC LIMIT 1";
        NSLog(@"开始查询最后一条记录的recordID，sql =: %@", sql);
        FMResultSet *resultSet = [db executeQuery:sql];
        if ([resultSet next]) {
            NSString *recordId = [resultSet stringForColumn:@"recordID"];
            record.recordID = recordId;
            NSLog(@"查询到数据：recordId = %@", recordId);
        } else {
            NSLog(@"未查询到数据");
        }
        [resultSet close];
        
        
        //插入数据到VideoRecord表
        sql = @"INSERT INTO VideoRecord (recordID, originVideoPath, transformVideoPath, updateTime) VALUES (?, ?, ?, ?)";
        NSLog(@"插入数据到VideoRecord表中，sql =: %@", sql);
        succed = [db executeUpdate:sql, record.recordID, record.originVideoPath, record.transformVideoPath, record.updateTime];
        if (succed) {
            NSLog(@"插入数据到VideoRecord表成功");
        } else {
            NSLog(@"插入数据到VideoRecord表失败");
        }
        
        
        //按时间排序，删除100条以外的数据，
        long maxNumber = 100;
        //查询超出的记录
        FMResultSet *deleteResultSet = [db executeQueryWithFormat:@"SELECT originVideoPath, transformVideoPath FROM VideoRecord WHERE recordID IN (SELECT recordID FROM VideoRecord ORDER BY updateTime DESC Limit %ld OFFSET %ld );", maxNumber, maxNumber];
        
        NSLog(@"删除路径下的文件");
        while ([deleteResultSet next]) {
            
            NSString *originVideoPath = [deleteResultSet stringForColumn:@"originVideoPath"];
            NSString *transformVideoPath = [deleteResultSet stringForColumn:@"transformVideoPath"];
            
            [self deleteFileWithRelativePath:originVideoPath];
            [self deleteFileWithRelativePath:transformVideoPath];
        }
        
        
        
        
        NSLog(@"删除VideoRecord超过第100条的数据");
        succed = [db executeUpdateWithFormat:@"DELETE FROM VideoRecord WHERE recordID IN (SELECT recordID FROM VideoRecord ORDER BY updateTime DESC Limit %ld OFFSET %ld );", maxNumber, maxNumber];
        
        
        if (succed) {
            NSLog(@"数据删除成功。");
        } else {
            NSLog(@"数据删除失败");
        }
        
    }];
}

//删除一条记录
- (void) deleteRecordItemWithRecordID:(NSString *) recordID {
    NSLog(@"删除一条Record记录，recordID = %@",recordID);
    
    __weak typeof(self) weakSelf = self;
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        
        NSString *deleteRecordSql = @"DELETE FROM Record WHERE recordID = ?;";
        NSLog(@"删除Record表中的记录，sql = :%@",deleteRecordSql);
        Boolean succed = [db executeUpdate:deleteRecordSql, recordID];
        if (succed) {
            NSLog(@"删除记录成功");
        } else {
            NSLog(@"删除记录失败");
        }
        
        //删除VideoRecord表中的记录
        [weakSelf deleteVideoRecordItemWithRecordID:recordID WithDatabase:db];
        
    }];
}


- (void) deleteVideoRecordItemWithRecordID:(NSString *) recordID WithDatabase:(FMDatabase *) db {
    NSLog(@"删除一条VideoRecord记录，recordID = %@", recordID);
    
    NSString *sql = @"QUERY * FROM VideoRecord WHERE recordID = ?;";
    NSLog(@"查询VideoRecord表中的记录，sql = :%@", sql);
    FMResultSet *resultSet = [db executeQuery:sql, recordID];
    
    NSLog(@"删除路径下的文件");
    while([resultSet next]) {
        NSString *originVideoPath = [resultSet stringForColumn:@"originVideoPath"];
        NSString *transformVideoPath = [resultSet stringForColumn:@"transformVideoPath"];
        
        [self deleteFileWithRelativePath:originVideoPath];
        [self deleteFileWithRelativePath:transformVideoPath];
    }
    
    sql = @"DELETE FROM VideoRecord WHERE recordID = ?;";
    NSLog(@"删除VideoRecord表中的记录，sql = :%@",sql);
    Boolean succed = [db executeUpdate:sql, recordID];
    if (succed) {
        NSLog(@"删除记录成功");
    } else {
        NSLog(@"删除记录失败");
    }
}

- (void) deleteVideoRecordItemWithRecordID:(NSString *) recordID {
    NSLog(@"删除一条VideoRecord记录，recordID = %@", recordID);
    
    __weak typeof(self) weakSelf = self;
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        
        [weakSelf deleteVideoRecordItemWithRecordID:recordID WithDatabase:db];
    }];
    
}


//分页查询数据 pageIndex从0开始
- (NSMutableArray<KiwipenRecord *> *) queryRecordDatasWithPageIndex:(NSInteger) pageIndex {
    NSLog(@"分页查询数据,第%ld页",(long) pageIndex);
    
    __block NSMutableArray<KiwipenRecord *> * dataArray = [NSMutableArray array];
    
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        
        long pageSize = 20;
        
        
        NSLog(@"默认没一页查询数据 %ld 个", pageSize);
        FMResultSet *result = [db executeQueryWithFormat:@"SELECT * FROM ( SELECT REcord.recordID, userID, deviceID, roleType, originLanguage, transformLuanguage, originText, transformText, originVideoPath, transformVideoPath, Record.updateTime FROM Record LEFT OUTER JOIN VideoRecord ON (Record.recordID = VideoRecord.recordID) ORDER BY Record.updateTime DESC LIMIT %ld OFFSET %ld * 1 ) ORDER BY updateTime ASC;", pageSize, pageIndex * pageSize];
        
        
        while ([result next]) {
            KiwipenRecord *record = [KiwipenRecord mj_objectWithKeyValues: [result resultDictionary]];
            [dataArray addObject:record];
            
            NSLog(@"获取到数据：%@", [record mj_keyValues]);
            
        }
        
    }];
    
    return dataArray;
}


- (KiwipenRecord *) queryRecordDataWithRecordID:(NSString *) recordID {
    NSLog(@"开始查询一条Record记录，recordID = %@", recordID);
    
    
    __block KiwipenRecord *result = nil;
    
    [self.queue inDatabase:^(FMDatabase * _Nonnull db) {
        
        
        NSString *sql = @"SELECT Record.recordID, userID, deviceID, roleType, originLanguage, transformLuanguage, originText, transformText, originVideoPath, transformVideoPath, Record.updateTime FROM Record LEFT OUTER JOIN VideoRecord ON (Record.recordID = VideoRecord.recordID) WHERE Record.recordID = ?;";
        
        NSLog(@"开始查询Record记录，sql = %@", sql);
        FMResultSet *resultSet = [db executeQuery:sql, recordID];
        
        if ([resultSet next]) {
            result = [KiwipenRecord mj_objectWithKeyValues:[resultSet resultDictionary]];
            
            NSLog(@"获取到数据: %@", [result mj_keyValues]);
        }
        
        [resultSet close];
        
    }];
    return result;
}

//根据相对路径删除Document目录下的文件
- (void) deleteFileWithRelativePath:(NSString *) path {
    NSLog(@"文件目录是：%@", path);
    if (path) {//删除对应的文件
        NSString *docDir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) firstObject];
        NSString *filePath = [docDir stringByAppendingPathComponent:path];
        
        NSError *error;
        NSFileManager *manager = [NSFileManager defaultManager];
        if ([manager fileExistsAtPath:filePath]) {
            if ([manager removeItemAtPath:filePath error:&error]) {
                NSLog(@"删除文件成功：%@",path);
            } else {
                NSLog(@"删除文件失败,错误是：%@",error);
            }
        }
    } else {
        NSLog(@"文件目录为空。");
    }
}

@end
